package cn.com.doone.common.uc.infrastructure.jdbc;

import java.security.NoSuchAlgorithmException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.com.doone.common.uc.domain.rs.IRegisterJdbcRepository;
import cn.com.doone.common.uc.domain.rs.ISyncJdbcRepository;
import cn.com.doone.common.uc.service.dto.UserGroup;
import cn.com.doone.common.uc.service.dto.UserGroupMember;
import cn.com.doone.common.uc.service.dto.UserInfo;
import cn.com.doone.common.uc.utils.DES3;
import cn.com.doone.common.uc.utils.PasswordUtils;
import cn.com.doone.common.uc.utils.PropertiesUtils;

@Repository("syncJdbcRepository")
public class SyncJdbcRepository extends AbstractJdbcRepository implements ISyncJdbcRepository  {

	@Autowired
	PropertiesUtils propertiesUtils;
	
	/**
	 * 租户编号是否存在
	 */
	@Override
	public boolean validTenantCode(String tenantCode) {
		final String sql = "SELECT COUNT(USER_GROUP_ID) FROM USER_GROUP WHERE TENANT_CODE = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {tenantCode}, Integer.class);
    	return count == 0 ? false : true;
	}
	
	/**
	 * 验证组编号是否存在
	 * 
	 * @param tenantCode
	 * @return
	 */
	public boolean validGroupCode(String groupCode) {
		final String sql = "SELECT COUNT(USER_GROUP_ID) FROM USER_GROUP WHERE GROUP_CODE = ?";
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {groupCode}, Integer.class);
    	return count == 0 ? false : true;
	}
	
	/**
	 * 新增组
	 */
	@Override
	public int addUserGroup(final UserGroup userGroup) {
		
		final String sql = "INSERT INTO USER_GROUP ("
							+ "FK_GROUP_ID, "
							+ "GROUP_CODE, "
							+ "GROUP_TYPE, "
							+ "GROUP_NAME, "
							+ "DESCRIPTION, "
							+ "TENANT_CODE, "
							+ "STATUS, "
							+ "UPDATE_TIME"
							+ ") VALUE (?,?,?,?,?,?,?,?)";
		
	    KeyHolder keyHolder = new GeneratedKeyHolder();
	    
	    jdbcTemplate.update(new PreparedStatementCreator() {
	    	@Override
	        public PreparedStatement createPreparedStatement(java.sql.Connection connection) throws SQLException {
	            PreparedStatement ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
	            // 父级ID
                ps.setInt(1, userGroup.getFkGroupId());
                // 组编号
                ps.setString(2, userGroup.getGroupCode());
                // 组类型
                ps.setString(3, userGroup.getGroupType());
                // 组名
                ps.setString(4, userGroup.getGroupName());
                // 描述
                ps.setString(5, userGroup.getDescription());
                // 租户码
                ps.setString(6, userGroup.getTenantCode());
                // 状态
                ps.setString(7, userGroup.getStatus());
                // 更新时间
                ps.setTimestamp(8, new java.sql.Timestamp(userGroup.getUpdateTime().getTime()));
				return ps;
	        }
	    }, keyHolder);
	    
	    return keyHolder.getKey().intValue();
	}

	@Override
	public void updateUserGroup(final UserGroup userGroup) {
		
		final String sql = "UPDATE USER_GROUP SET "
							+ "GROUP_TYPE = ?,"
							+ "GROUP_NAME = ?, "
							+ "DESCRIPTION = ?, "
							+ "UPDATE_TIME = ?"
						+ "WHERE "
							+ "GROUP_CODE = ?";
		
    	jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                // 组类型
                ps.setString(1, userGroup.getGroupType());
                // 组名
                ps.setString(2, userGroup.getGroupName());
                // 组描述
                ps.setString(3, userGroup.getDescription());
                // 更新时间
                ps.setTimestamp(4, new java.sql.Timestamp(userGroup.getUpdateTime().getTime()));
                // 组ID
                ps.setString(5, userGroup.getGroupCode());
            }
        });
	}

	@Override
	public void deleteUserGroup(final UserGroup userGroup) {
		
		final String sql = "UPDATE USER_GROUP SET "
							+ "STATUS = ?,"
							+ "UPDATE_TIME = ?"
						+ "WHERE "
							+ "GROUP_CODE = ?";
		
		jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
			    // 组类型
			    ps.setString(1, userGroup.getStatus());
			    // 更新时间
			    ps.setTimestamp(2, new java.sql.Timestamp(userGroup.getUpdateTime().getTime()));
			    // 组ID
			    ps.setString(3, userGroup.getGroupCode());
			}
		});
		
	}
	
	/**
	 * 根据编号获取组信息
	 * 
	 * @param groupCode
	 * @return
	 */
	public Map<String, Object> findUserGroupByGroupCode(String groupCode) {
		
		final String sql = " SELECT" +
								" USER_GROUP_ID," +
								" FK_GROUP_ID," +
								" GROUP_CODE," +
								" GROUP_TYPE," +
								" GROUP_NAME," +
								" DESCRIPTION," +
								" TENANT_CODE," +
								" `STATUS`," +
								" UPDATE_TIME" +
							" FROM" +
								" user_group" +
							" WHERE" +
								" GROUP_CODE = ?";
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, groupCode); 
		return list.isEmpty() ? null : list.get(0);
		
	}

	@Override
	public int addUserInfo(final UserInfo userInfo) {
		
		final String sql = "INSERT INTO USER_INFO (USER_ACCOUNT, PASSWORD, ENCRYPTION_TYPE, EMAIL, MOBILE_PHONE, NICKNAME, REALNAME, SEX, STATUS, UPDATE_TIME,P_CODE) VALUE (?,?,?,?,?,?,?,?,?,?,?)";
	    KeyHolder keyHolder = new GeneratedKeyHolder();
	    jdbcTemplate.update(new PreparedStatementCreator() {
	    	@Override
	        public PreparedStatement createPreparedStatement(java.sql.Connection connection) throws SQLException {
	            PreparedStatement ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
	            // 账号
                ps.setString(1, userInfo.getUserAccount());
                // 密码
                try {
					ps.setString(2, PasswordUtils.openLdapMD5(userInfo.getPassword()));
				} catch (NoSuchAlgorithmException e) {
					throw new SQLException();
				}
                // 密码加密版本
                ps.setString(3, "2");
                // 邮件
                ps.setString(4, userInfo.getEmail());
                // 手机号
                ps.setString(5, userInfo.getMobilePhone());
                // 昵称
                ps.setString(6, userInfo.getNickname());
                // 真实姓名
                ps.setString(7, userInfo.getRealname());
                // 性别
                ps.setString(8, userInfo.getSex());
                // 状态
                ps.setString(9, userInfo.getStatus());
                
                ps.setTimestamp(10, new java.sql.Timestamp(userInfo.getUpdateTime().getTime()));
                try {
					ps.setString(11, DES3.encode(userInfo.getPassword(), propertiesUtils.getSecretKey()));
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return ps;
	        }
	    }, keyHolder);
	    
	    return keyHolder.getKey().intValue();
	}

	@Override
	public void updateUserInfo(final UserInfo userInfo) {
		final String sql = "UPDATE"
							+ " USER_INFO "
						+ " SET "
							+ " EMAIL = ?,"
							+ " SEX = ?,"
							+ " NICKNAME = ?,"
							+ " REALNAME = ?,"
							+ " UPDATE_TIME = ?"
						+ " WHERE "
							+ " USER_INFO_ID = ?";

		jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
			    // 邮箱地址
				ps.setString(1, userInfo.getEmail());
			    // 性别
				ps.setString(2, userInfo.getSex());
				// 昵称
				ps.setString(3, userInfo.getNickname());
				// 真实姓名
				ps.setString(4, userInfo.getRealname());
				// 更新时间
			    ps.setTimestamp(5, new java.sql.Timestamp(userInfo.getUpdateTime().getTime()));
			    // 用户ID
			    ps.setInt(6, userInfo.getUserInfoId());
			}
		});
	}
	
	public void updateDooneCode(final UserInfo userInfo) {
		final String sql = "UPDATE"
							+ " USER_INFO "
						+ " SET "
							+ " DOONE_CODE = ?"
						+ " WHERE "
							+ " USER_INFO_ID = ?";

		jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
			    // 邮箱地址
				ps.setInt(1, userInfo.getDooneCode());
			    // 用户ID
			    ps.setInt(2, userInfo.getUserInfoId());
			}
		});
	}

	/**
	 * 删除用户信息
	 */
	@Override
	public void deleteUserInfo(final UserInfo userInfo) {
		
		final String sql = "UPDATE"
							+ " USER_INFO "
						+ " SET "
							+ " STATUS = ?,"
							+ " UPDATE_TIME = ?"
						+ " WHERE "
							+ " USER_INFO_ID = ?";

		jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
			    // 状态
				ps.setString(1, userInfo.getStatus());
				// 更新时间
			    ps.setTimestamp(2, new java.sql.Timestamp(userInfo.getUpdateTime().getTime()));
			    // 用户ID
			    ps.setInt(3, userInfo.getUserInfoId());
			}
		});
		
	}

	@Override
	public void addUserGroupMember(final UserGroupMember userGroupMember) {
		final String sql = "INSERT INTO USER_GROUP_MEMBER (USER_INFO_ID, USER_GROUP_ID, UPDATE_TIME, STATUS) VALUE (?,?,?,?)";
    	jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, userGroupMember.getUserInfoId());
                ps.setInt(2, userGroupMember.getUserGroupId());
                ps.setTimestamp(3, new java.sql.Timestamp(userGroupMember.getUpdateTime().getTime()));
                ps.setString(4, userGroupMember.getStatus());
            }
        });
	}
	
	/**
	 * 更新组用户
	 */
	@Override
	public void updateUserGroupMember(final UserGroupMember userGroupMember) {
		
		final String sql = "UPDATE USER_GROUP_MEMBER SET "
							+ " STATUS = ?,"
							+ " UPDATE_TIME = ?"
						+ " WHERE "
							+ " USER_INFO_ID = ?"
						+ " and "
							+ " USER_GROUP_ID = ?";

		jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
			    // 组类型
			    ps.setString(1, userGroupMember.getStatus());
			    // 更新时间
			    ps.setTimestamp(2, new java.sql.Timestamp(userGroupMember.getUpdateTime().getTime()));
			    // 用户ID
			    ps.setInt(3, userGroupMember.getUserInfoId());
			    // 组ID
			    ps.setInt(4, userGroupMember.getUserGroupId());
			}
		});
		
		
	}

	/**
	 * 删除组用户
	 */
	@Override
	public void deleteUserGroupMember(final UserGroupMember userGroupMember) {
		
		final String sql = "UPDATE USER_GROUP_MEMBER SET "
							+ " STATUS = ?,"
							+ " UPDATE_TIME = ?"
						+ " WHERE "
							+ " USER_INFO_ID = ?"
						+ " and "
							+ " USER_GROUP_ID = ?";

		jdbcTemplate.update(sql, new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
			    // 组类型
			    ps.setString(1, userGroupMember.getStatus());
			    // 更新时间
			    ps.setTimestamp(2, new java.sql.Timestamp(userGroupMember.getUpdateTime().getTime()));
			    // 用户ID
			    ps.setInt(3, userGroupMember.getUserInfoId());
			    // 组ID
			    ps.setInt(4, userGroupMember.getUserGroupId());
			}
		});
		
	}
	
	/**
	 * 查看组用户
	 */
	@Override
	public boolean findUserGroupMember(final UserGroupMember userGroupMember) {
		
		final String sql = "SELECT"
							+ "	COUNT(USER_GROUP_MEMBER_ID)"
						+ " FROM"
							+ " USER_GROUP_MEMBER "
						+ " WHERE "
							+ " USER_INFO_ID = ?"
						+ " and "
							+ " USER_GROUP_ID = ?";

		
    	final int count = jdbcTemplate.queryForObject(sql, new Object[] {userGroupMember.getUserInfoId(), userGroupMember.getUserGroupId()}, Integer.class);
		
		return count == 0 ? false : true;
	}

	/**
	 * 根据APP_CODE查看租户
	 * 
	 * @param appCode
	 * @return
	 */
	@Override
	public List<Map<String, Object>> findTenantByAppCode(String appCode) {
		final String sql = " SELECT" +
						       " userGroup.*" +
						   " FROM" +
						   	   " app_info appInfo," +
						   	   " user_group userGroup," +
						   	   " user_group_app_authorize userGroupAppAuthorize" +
						   " WHERE" +
						       " appInfo.APP_INFO_ID = userGroupAppAuthorize.APP_INFO_ID" +
						   " AND userGroup.USER_GROUP_ID = userGroupAppAuthorize.USER_GROUP_ID" +
				           " AND appInfo.APP_CODE = ?" +
				           " AND userGroup.FK_GROUP_ID = 0";
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, appCode); 
		return list;
	}

	/**
	 * 根据租户
	 * 
	 * @param groupCode
	 * @param updateTime
	 * @return
	 */
	@Override
	public List<Map<String, Object>> findGroupByTenantAndUpdateTime(final String groupCode, final String updateTime) {
		
		// TODO 有SQL注入风险
		final String sql = " SELECT "
						 + " t.GROUP_CODE as groupCode,"
						 + " t.GROUP_TYPE as groupType,"
						 + " t.GROUP_NAME as groupName,"
						 + " t.DESCRIPTION as description,"
						 + " t.TENANT_CODE as tenantCode,"
						 + " T.STATUS as status,"
						 + " DATE_FORMAT(T.UPDATE_TIME,'%Y-%m-%d %T') as updateTime"
						 + " FROM"
				             + " user_group t"
				         + " WHERE"
				             +" GROUP_CODE LIKE '" + groupCode + "%'"
				          + "AND "
						    + " UPDATE_TIME >= '" + updateTime + "'"
						  + " ORDER BY t.GROUP_CODE";
		
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		return list;
	}
	
	/**
	 * 查询未更新的用户
	 * 
	 * @param groupCode
	 * @param updateTime
	 * @return
	 */
	public List<Map<String, Object>> findUserByTenantAndUpdateTime(String groupCode, String updateTime) {
		//TODO 有SQL注入风险
		final StringBuffer sql = new StringBuffer()
				.append("SELECT")
				.append(" DISTINCT userInfo.USER_INFO_ID as userInfoId,")
				.append(" userInfo.DOONE_CODE as dooneCode,")
				.append(" userInfo.USER_ACCOUNT as userAccount,")
				.append(" userInfo.EMAIL as email,")
				.append(" userInfo.MOBILE_PHONE as mobilePhone,")
				.append(" userInfo.SEX as sex,")
				.append(" userInfo.P_CODE as password,")
				.append(" userInfo.`STATUS` as status,")
				.append(" userInfo.NICKNAME as nickname,")
				.append(" userInfo.REALNAME as realname,")
				.append(" DATE_FORMAT(userInfo.UPDATE_TIME,'%Y-%m-%d %T') as updateTime,")
				.append(" userGroup.GROUP_CODE as groupCode")
				.append(" FROM ")
				.append(" user_group userGroup,")
				.append(" user_info userInfo,")
				.append(" user_group_member userGroupMember")
				.append(" WHERE")
				.append(" userGroup.USER_GROUP_ID = userGroupMember.USER_GROUP_ID")
				.append(" AND")
				.append(" userInfo.USER_INFO_ID = userGroupMember.USER_INFO_ID")
				.append(" AND")
				.append(" userGroup.GROUP_CODE LIKE '").append(groupCode).append("%'")
				.append(" AND")
				.append(" userInfo.UPDATE_TIME >= '").append(updateTime).append("'")
				;
		
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString());
		return list;
		
	}
	
	/**
	 * 查询未更新的组成员
	 * 
	 * @param groupCode
	 * @param updateTime
	 * @return
	 */
	public List<Map<String, Object>> findGroupMemberByTenantAndUpdateTime(String groupCode, String updateTime) {
		
		// TODO 有SQL注入风险
		final String sql = "SELECT"
						+ " userGroupMember.USER_INFO_ID as userInfoId,"
						+ " userGroup.GROUP_CODE as groupCode,"
						+ " userGroupMember.STATUS as status,"
						+ " userGroupMember.USER_GROUP_ID as userGroupId,"
						+ " DATE_FORMAT(userGroupMember.UPDATE_TIME,'%Y-%m-%d %T') as updateTime"
						+ " FROM"
						 	+ " user_group userGroup,"
						 	+ " user_info userInfo,"
						 	+ " user_group_member userGroupMember"
						+ " WHERE"
							+ " userGroup.USER_GROUP_ID = userGroupMember.USER_GROUP_ID"
						+ " AND"
							+ " userInfo.USER_INFO_ID = userGroupMember.USER_INFO_ID"
						+ " AND"
							+ " userGroup.GROUP_CODE LIKE '" + groupCode + "%'"
						+ " AND"
							+ " userGroupMember.UPDATE_TIME >= '" + updateTime + "'"
						+ " ORDER BY userGroup.GROUP_CODE";
		
		final List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
		return list;
		
	}

}
